How to encode data field into table using MS Access VBA

This article explains fundamental concept to encode data field in a MS Access table. As shown below in figure Fig-1.1 we will create a form named detailfrm and it include buttons named cmdencode with caption insert and another button named Fetch with caption cmddata. Details have been shown in figure below.

First we will create a table named tblperson along with necessary field to facilitate this process. Detail has been shown in figure Fig-1.2.

Now in next step we store some details into table named tblperson .Using form detailfrm make an entry into table because we have associated an on click event with insert control of this from hence usr_password field always stores encrypted value in it. Details has been shown in figure Fig 1.3.

In order to fetch details of recently created record click on fetch. A dialog box appear asking ID value to fetch corresponding record. Details has been shown in figure Fig-1.4.

We have associated an on click event with this control hence encoded password shown in decoded format. Details has been mention in figure Fig-1.5.

VBA Code

VBA code for On Click event of insert control:-

Private Sub cmdencode_Click()
Dim strpassword As String
Dim Db As DAO.Database
Set Db = CurrentDb
strpassword = encryptPW(Me.txtpass)
Db.Execute "insert into tblperson(usr_name,usr_password) values( '" & Me.txtname & "', '" & strpassword & "' )"
MsgBox "Encoded Password Stored into table", vbOKOnly
Me.txtname = Nothing
Me.txtpass = Nothing
End Sub

'VBA code for On Click event of fetch control
Private Sub cmddata_Click()
Me.txtname = Nothing
Me.txtpass = Nothing
Dim strdecpassword As String
Dim i As Integer
Dim Db As DAO.Database
Dim rst As DAO.Recordset
i = InputBox("Enter ID to fetch corresponding data", "Getting values")
Set Db = CurrentDb
Set rst = Db.OpenRecordset("select * from tblperson where ID = " & i)
strdecpassword = decryptPW(rst!usr_password)
MsgBox "Decoded Password Field", vbOKOnly
Me.txtname = rst!usr_name
Me.txtpass = strdecpassword
End Sub

'VBA code for On Click event of fetch control
Private Sub cmddata_Click()
Me.txtname = Nothing
Me.txtpass = Nothing
Dim strdecpassword As String
Dim i As Integer
Dim Db As DAO.Database
Dim rst As DAO.Recordset
i = InputBox("Enter ID to fetch corresponding data", "Getting values")
Set Db = CurrentDb
Set rst = Db.OpenRecordset("select * from tblperson where ID = " & i)
strdecpassword = decryptPW(rst!usr_password)
MsgBox "Decoded Password Field", vbOKOnly
Me.txtname = rst!usr_name
Me.txtpass = strdecpassword
End Sub

Private Sub cmdencode_Click()
Dim strpassword As String
Dim Db As DAO.Database
Set Db = CurrentDb
strpassword = encryptPW(Me.txtpass)
Db.Execute "insert into tblperson(usr_name,usr_password) values( '" & Me.txtname & "', '" & strpassword & "' )"
MsgBox "Encoded Password Stored into table", vbOKOnly
Me.txtname = Nothing
Me.txtpass = Nothing
End Sub